/*
	EXEC SP_CREATE_UPDATE_SORT_LOT_BIN 'IP5160U','NC6866.00',15000,15000,'05/22/2007','SPIL','SORT', '25', null
*/
ALTER   PROC SP_CREATE_UPDATE_SORT_LOT_BIN 
(
	@PART_NUMBER 		VARCHAR(50), 
	@LOT_NUMBER 		VARCHAR(50),
	@TOTAL_GOOD_DIE    	INT,
	@TOTAL_PROBE_DIE	INT,
	@YIELD  	        FLOAT,
	@DATE_IN     		VARCHAR(50), 
	@VENDOR_NAME 		VARCHAR(50),
	@PS_TYPE_NAME 		VARCHAR(50),
	@WAFER_QTY_IN 		VARCHAR(50),
	@TESTER   		    VARCHAR(50),
	@TEST_PGM    		VARCHAR(50),
    @IS_SHIP_INFO_LOT	INT,
	@DATE_OUT     		VARCHAR(50),
	@LOT_ID BIGINT OUTPUT 
 )
 
 AS
 BEGIN
 
 	DECLARE @processStepId BIGINT 
 	DECLARE @locationId INT
 	DECLARE @TEMP_LOT_ID BIGINT
	DECLARE @LOT_CATEGORY VARCHAR(10)
	DECLARE @RETURN_DUMMY_LOT_ID BIGINT
	DECLARE @TARGET_YIELD FLOAT

	IF @YIELD IS NULL OR @YIELD = 0.0
	BEGIN
		SET @YIELD = ROUND( ( @TOTAL_GOOD_DIE * 100.00 ) / @TOTAL_PROBE_DIE , 2)
	END
	 	
	IF 	@DATE_IN IS NULL OR LTRIM(RTRIM(@DATE_IN)) = ''
	BEGIN
			SET @DATE_IN = CURRENT_TIMESTAMP
	END	

	SELECT @locationId = LOCATION_ID FROM LOCATION WHERE L_TYPE = @PS_TYPE_NAME AND L_VENDOR_NAME = @VENDOR_NAME
	
	SELECT @processStepId = PROCESS_STEP.PROCESSSTEP_ID FROM PRODUCT,PROCESS_STEP WHERE PRODUCT.PART_NUMBER = @PART_NUMBER AND PS_TYPE_NAME = @PS_TYPE_NAME AND PRODUCT.PROCESSSTEP_ID=PROCESS_STEP.PROCESSSTEP_ID 
	
	IF @processStepId IS NULL
	BEGIN
		SELECT @processStepId = PROCESS_STEP.PROCESSSTEP_ID FROM PRODUCT_MAP_TABLE,PROCESS_STEP WHERE PRODUCT_MAP_TABLE.PART_NUMBER = @PART_NUMBER  AND PS_TYPE_NAME= @PS_TYPE_NAME AND PRODUCT_MAP_TABLE.PROCESSSTEP_ID=PROCESS_STEP.PROCESSSTEP_ID
		
		IF @processStepId IS NULL
		BEGIN
			--SELECT * FROM NOTIFICATION_ALERT_TABLE ORDER BY TIMESTAMP DESC
			--For Discrepancy Lot Notification Alert
			IF ( SELECT COUNT(*) FROM NOTIFICATION_ALERT_TABLE WHERE PART_NUMBER = @PART_NUMBER AND LOT_NUMBER = @LOT_NUMBER and Process_Type = 'SORT' AND VENDOR_NAME = @VENDOR_NAME  AND IS_USER_NOTIFIED = 1 ) = 0 
			BEGIN
				INSERT INTO Notification_Alert_Table ( Part_Number, Lot_Number, Fab_Lot_Number, Vendor_Name, Process_Type, Notification_Name, Wafer_Qty, Die_Qty, Wafer_id, Status,TimeStamp )
					VALUES ( @part_number, @lot_number, @lot_number, @vendor_name, 'SORT','Discrepancy Lot Notification', @WAFER_QTY_IN, @TOTAL_GOOD_DIE, null, 'Open', CURRENT_TIMESTAMP )
			END

			SET @RETURN_DUMMY_LOT_ID = 0
			UPDATE LOT_TABLE SET LOT_NUMBER = 'LOT_NUMBER' WHERE LOT_NUMBER = 'LOT_NUMBER'
			SET @LOT_ID = @RETURN_DUMMY_LOT_ID
			SELECT @LOT_ID
			return 
		END
	END	
	
	SELECT @TARGET_YIELD = ISNULL(Parameter_Value, 94) FROM PROCESS_PARAMETER WHERE ProcessStep_Id = @processStepId AND Parameter_Key = 'Target Yield'
	IF @TARGET_YIELD = '' OR @TARGET_YIELD = '0.00'
	BEGIN
		SET @TARGET_YIELD = CAST( 94  AS FLOAT )
	END

	IF @YIELD < @TARGET_YIELD AND @TOTAL_GOOD_DIE > 1000
	BEGIN
		IF ( SELECT COUNT(*) FROM NOTIFICATION_ALERT_TABLE WHERE PART_NUMBER = @PART_NUMBER AND LOT_NUMBER = @LOT_NUMBER AND Notification_Name = 'WS Low Yield Notification' AND VENDOR_NAME = @VENDOR_NAME AND PROCESS_TYPE = 'SORT' AND  IS_USER_NOTIFIED = 1 ) = 0 
		BEGIN
			INSERT INTO Notification_Alert_Table ( Part_Number, Lot_Number, Fab_Lot_Number, Vendor_Name, Process_Type, Notification_Name, Die_Qty,Qty_out, Yield, Status,TimeStamp )
				VALUES ( @part_number, @lot_number, @lot_number, @vendor_name, 'SORT','WS Low Yield Notification', @TOTAL_PROBE_DIE, @TOTAL_GOOD_DIE, @YIELD,'Open', CURRENT_TIMESTAMP )
		END
	END 	

 	SELECT @TEMP_LOT_ID = LOT_ID FROM LOT_TABLE WHERE PROCESSSTEP_ID = @processStepId AND LOCATION_ID = @locationId AND LOT_NUMBER = @LOT_NUMBER
 	
	IF @TEMP_LOT_ID IS NOT NULL
 	BEGIN
		UPDATE LOT_TABLE SET Good_Die_Qty = @TOTAL_GOOD_DIE, Wafer_Qty_In = @WAFER_QTY_IN, Wafer_Qty_Out = @WAFER_QTY_IN, DATE_OUT = ISNULL(DATE_OUT, ISNULL(@DATE_OUT,@DATE_IN) ), LOT_CURRENT_STATUS = 'Close' WHERE LOT_ID = @TEMP_LOT_ID
		IF ( (SELECT COUNT(*) FROM SORT_LOT WHERE LOT_ID = @TEMP_LOT_ID ) = 0 )
		BEGIN
			-- Insert select * from Sort_Lot
  			insert into Sort_Lot(Lot_ID, SL_YIELD,SL_TESTER, SL_TEST_PROGRAM, Timestamp ) values (@TEMP_LOT_ID, @YIELD, @TESTER, @TEST_PGM, CURRENT_TIMESTAMP )
		END
		ELSE
		BEGIN
			
			update Sort_Lot  SET SL_YIELD = ISNULL(@YIELD,SL_YIELD) , SL_TESTER = ISNULL(@TESTER,SL_TESTER), SL_TEST_PROGRAM = ISNULL(@TEST_PGM,SL_TEST_PROGRAM ), Timestamp = CURRENT_TIMESTAMP WHERE LOT_ID = @TEMP_LOT_ID
		END

		IF @IS_SHIP_INFO_LOT = 0
		BEGIN
				/*Closing the Previous Transit Lots when ever Lots are came in to Next Process*/
				EXEC SP_CLOSE_PREVIOUSPROCESS_AND_TRANSITLOTS @lot_number, @lot_number, @WAFER_QTY_IN, @DATE_IN
		END

		SET @LOT_ID = @TEMP_LOT_ID
		SELECT @LOT_ID

		/*SELECT @LOT_CATEGORY = LOT_CATEGORY from FN_GET_LOT_CATEGORY ( @lot_id, @lot_number, @processStepId, 'Sort')	
		IF @LOT_CATEGORY IS NOT NULL
		BEGIN
			UPDATE LOT_TABLE SET LOT_CATEGORY = @LOT_CATEGORY WHERE LOT_ID = @LOT_ID
		END*/			
 		RETURN
 	END
 
 	IF @processStepId IS NOT NULL AND @TEMP_LOT_ID IS NULL
 	BEGIN
		insert into Lot_Table (ProcessStep_ID, Location_ID, Lot_Number, Fab_Lot_Number, Vendor_Lot_Number, Wafer_Qty_In, Date_In, Wafer_Qty_Out, Date_Out, Good_Die_Qty, Lot_Current_Status, Timestamp,Lot_Entered_By, Lot_Category) 
  			values (@processStepId, @locationId, @lot_number, @lot_number, @lot_number, @WAFER_QTY_IN, @DATE_IN, @WAFER_QTY_IN, ISnULL(@DATE_OUT,@DATE_IN), @TOTAL_GOOD_DIE, 'Close', CURRENT_TIMESTAMP, 1, 'PROD'  )
  		select @lot_id = @@IDENTITY
  		
 		-- Insert select * from Sort_Lot
  		insert into Sort_Lot(Lot_ID, SL_YIELD,SL_TESTER, SL_TEST_PROGRAM, Timestamp ) values (@lot_id, @YIELD, @TESTER, @TEST_PGM, CURRENT_TIMESTAMP )
		
		-- For New Lot Notification Alert
		INSERT INTO Notification_Alert_Table ( Part_Number, Lot_Number, Fab_Lot_Number, Vendor_Name, Process_Type, Notification_Name, Wafer_Qty, Die_Qty, Wafer_id, Status, TimeStamp )
		VALUES ( @part_number, @lot_number, @lot_number, @vendor_name, 'SORT','New Lot Notification', @WAFER_QTY_IN, @TOTAL_GOOD_DIE, null, 'Open', CURRENT_TIMESTAMP )						

		IF @IS_SHIP_INFO_LOT = 0
		BEGIN
 			/*Closing the Previous Transit Lots when ever Lots are came in to Next Process*/
			EXEC SP_CLOSE_PREVIOUSPROCESS_AND_TRANSITLOTS @lot_number, @lot_number, @WAFER_QTY_IN, @DATE_IN
		END
   		
 	END	
 	
 	SELECT @LOT_ID
	RETURN
 
 END
 